Amazon Athena Webアクセスログ、CloudFrontのログ、CloudTrailのログのパーティション設定を自動化する
はじめに
Webアクセスログ（ELB）、CloudFrontのログ、CloudTrailのログは、YYYY/MM/DD形式のフォルダの下に自動的に保存されます。Amazon Athenaは、S3上のログファイルに対してテーブル定義して、クエリを実行できます。しかし、大量のログファイルを高速かつコスト効率よくクエリするにはパーティションを設定する必要があります。今回はこの煩雑なパーティション設定を自動化する方法をご紹介します。
カラム名あり（Hive互換）とカラム名なしのパーティション
パーティションは、カラム名あり（Hive互換）パーティションとカラム名なしパーティションの２つに分類されます。
カラム名あり（Hive互換）パーティションは、フォルダ名がキーバリュー形式になっており、例えばyear=YYYY/month=MM/day=DD形式で保存されています。この形式はMSCK REPIRE TABLEを実行するとフォルダを再帰的にスキャンして、パーティションを自動設定できます。
一方、カラム名なしパーティションは、YYYY/MM/DD形式のフォルダの下に自動的に保存されます。
$ aws s3 ls s3://mybucket/devio2017-handson/07-elblogs/ --recursive 2017-08-21 12:42:42 11789573 devio2017-handson/07-elblogs/2015/01/01/part-r-00000-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:42:45 9012723 devio2017-handson/07-elblogs/2015/01/02/part-r-00006-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:42:48 11360522 devio2017-handson/07-elblogs/2015/01/03/part-r-00012-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:42:50 11899582 devio2017-handson/07-elblogs/2015/01/03/part-r-00017-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:42:53 8321364 devio2017-handson/07-elblogs/2015/01/04/part-r-00023-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:42:55 7864475 devio2017-handson/07-elblogs/2015/01/05/part-r-00029-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:42:56 9148117 devio2017-handson/07-elblogs/2015/01/06/part-r-00035-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2017-08-21 12:43:00 10019678 devio2017-handson/07-elblogs/2015/01/07/part-r-00041-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt ：
Webアクセスログ（ELB）、CloudFrontのログ、CloudFrontのログ、Amazon Kinesis Data Firehoseなどはこの形式で保存されます。この形式はALTER TABLE ADD PARTITIONをパーティションごとに設定しなければなりません。この数が数百〜数万となると手作業では不可能です。以降では、ApacheのWebアクセスログを例に自動化の手順を解説します。なお、awscliとjqについては事前にインストールしてください。
例.ApacheのWebアクセスログのテーブルに対してパーティション設定する
テーブル定義は、以前紹介したAmazon Athena RegexSerDe を利用して CLB ログ / Apache Web のアクセスログを探索するのとおりです。年月日（year, month, day）にてパーティション設定しています。
CREATE EXTERNAL TABLE IF NOT EXISTS access_logs (
request_timestamp string,
elb_name string,
client_addrport string,
client_ip string,
client_port int,
backend_addrport string,
backend_ip string,
backend_port int,
request_processing_time decimal(8,6),
backend_processing_time decimal(8,6),
response_processing_time decimal(8,6),
elb_status_code string,
backend_status_code string,
received_bytes int,
sent_bytes int,
request string,
user_agent string,
ssl_cipher string,
ssl_protocol string
)
PARTITIONED BY (
year string,
month string,
day string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' = '([^ ]*) ([^ ]*) (([0-9.]*):([0-9]*)|-) (([0-9.]*):([0-9]*)|-) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\\"[^\\"]*\\") (\\"[^\\"]*\\") ([^ ]*) ([^ ]*)[ ]*$'
)
LOCATION 's3://mybucket/devio2017-handson/07-clblogs'
TBLPROPERTIES ('has_encrypted_data'='false');
パーティション設定を自動生成する
では、どうやってパーティション設定を自動生成するかというと、MSCK REPIRE TABLEと同様にS3をスキャンして、ALTER TABLE ADD PARTITIONを自動生成します。自動生成した結果からピンポイントでパーティション設定したい場合などは、こちらのほうが使いやすいと思います。
- dbname: dbnameを指定します
- tablename: tablenameを指定します
- s3bucket: データが保存されているバケットを指定します
- s3prefix: フォルダのパスを指定します
- profile: プロファイルを指定します（オプション）
#!/bin/sh
# Setting
dbname=default
tablename=access_logs
s3bucket=mybucket
s3prefix=devio2017-handson/07-elblogs
#profile="--profile=example_profile"
# get paths
paths=$(aws s3api list-objects --bucket ${s3bucket} --prefix ${s3prefix} ${profile} | grep -v '\$folder\$' | jq ".Contents[].Key" | awk -v FS='/' -v OFS='/' '{print $(NF-4), $(NF-3), $(NF-2), $(NF-1)}' | awk '!a[$0]++' | sort)
# generate ddl
for path in ${paths}
do
year=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $2}')
month=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $3}')
day=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $4}')
echo "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';"
done
exit 0
上記のスクリプトを実行すると、以下のように自動生成されます。Athenaのコンソールは、複数のクエリをまとめて実行できないので、SQL WorkbenchなどのツールからSQL実行することになります。
$ ./accesslog_partitioner.sh ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='01') LOCATION 's3://mybucket/07-elblogs/2015/01/01/'; ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='02') LOCATION 's3://mybucket/07-elblogs/2015/01/02/'; ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='03') LOCATION 's3://mybucket/07-elblogs/2015/01/03/'; ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='04') LOCATION 's3://mybucket/07-elblogs/2015/01/04/'; ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='05') LOCATION 's3://mybucket/07-elblogs/2015/01/05/'; ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='06') LOCATION 's3://mybucket/07-elblogs/2015/01/06/'; ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='07') LOCATION 's3://mybucket/07-elblogs/2015/01/07/'; ：
パーティション設定を自動設定する
完全に自動実行したい場合は、以下のようにawscliでDDLを同期的に実行します。(同期的に実行する方法はAWS CLI で Athena のクエリ実行を同期的に行うを参考にさせていただきました)
- dbname: dbnameを指定します
- tablename: tablenameを指定します
- s3bucket: データが保存されているバケットを指定します
- s3prefix: フォルダのパスを指定します
- outputlocation: 実行結果を保存するディレクトリを指定します
- profile: プロファイルを指定します（オプション）
#!/bin/sh
# Setting
dbname=default
tablename=access_logs
s3bucket=mybucket
s3prefix=devio2017-handson/07-elblogs
outputlocation=s3://s3-staging-dir/tmp/
#profile="--profile=example_profile"
MAX_RETRY=30
FETCH_INTERVAL_SECONDS=1
# get paths
paths=$(aws s3api list-objects --bucket ${s3bucket} --prefix ${s3prefix} ${profile} | grep -v '\$folder\$' | jq ".Contents[].Key" | awk -v FS='/' -v OFS='/' '{print $(NF-4), $(NF-3), $(NF-2), $(NF-1)}' | awk '!a[$0]++' | sort)
# generate ddl
for path in ${paths}
do
year=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $2}')
month=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $3}')
day=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $4}')
echo "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';"
query_execution_id=$(aws athena start-query-execution --query-string "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';" --result-configuration OutputLocation="${outputlocation}" ${profile} | jq -r '.QueryExecutionId')
try_cnt=1
while true
do
if [ ${try_cnt} -ge ${MAX_RETRY} ] ; then
echo "Error: timeout" >&2
break
else
sleep ${FETCH_INTERVAL_SECONDS}
fi
query_execution_result=$(aws athena get-query-execution --query-execution-id ${query_execution_id} ${profile})
query_state=$(echo ${query_execution_result} | jq -r '.QueryExecution.Status.State')
if [ ${query_state} = 'SUCCEEDED' ] ; then
echo "${query_state}"
break
elif [ ${query_state} = 'FAILED' ] ; then
echo "${query_state}" >&2
break
fi
try_cnt=$(expr ${try_cnt} + 1)
done
done
exit 0
実行すると、DDLと実行結果（SUCCEEDED、FAILED, Error: timeout）を表示します。すでにパーティションが登録済みの場合は、FAILEDになります。
$ ./accesslog_partitioner.sh ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='01') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/01/'; SUCCEEDED ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='02') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/02/'; SUCCEEDED ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='03') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/03/'; SUCCEEDED ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='04') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/04/'; SUCCEEDED ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='05') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/05/'; SUCCEEDED ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='06') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/06/'; SUCCEEDED ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='07') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/07/'; SUCCEEDED ：
最後に
Amazon Athena とブログタイトルに書きましたが、テーブル定義（メタデータ）の登録先は Glue Data Catalog ですので、Amazon Redshift Spectrum、Amazon EMR、AWS Glueにおいても同様に利用可能です。
参考
Webアクセスログ、CloudFrontのログ、CloudFrontのログに関するテーブル定義につきましては、以下のブログを御覧ください。
Amazon Athena RegexSerDe を利用して CLB ログ / Apache Web のアクセスログを探索する
![[アップデート]CloudTrail Lake で高度なイベントセレクターがサポートされました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-64f6cd71568d228b1e7f3831e5287d75/1b5c0e8e5797b4bff5913d5033b03348/aws-cloudtrail)
